/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.OnlineHouseLeaseSystem.common;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.ArrayList;

import com.OnlineHouseLeaseSystem.database.DBManager;

/**
 *
 * @author gas1121
 */
public class HouseManager {
	public static final String USER_ID = "user_id";
	public static final String HOUSE_ID = "house_id";
	public static final String HOUSE_TITLE = "house_title";
	public static final String REGION = "region";
	public static final String HOUSE_TYPE = "house_type";
	public static final String HOUSE_ADDRESS = "house_address";
	public static final String RENT = "rent";
	public static final String LOOK_TIME = "look_time";
	public static final String DESCRIPTION = "description";
	public static final String DIRECTION = "direction";
	public static final String PROPERTY_MANAGEMENT_COMPANY="property_management_company";
	public static final String COMPLETED_DATE = "completed_date";
	public static final String FLOOR = "floor";
	public static final String SUPPORTING_FACILITY="supporting_facility";
	public static final String MARK = "mark";
	public static final String WATCH = "watch";
	public static final String ALLOW = "allow";
	public static final String LEASE = "lease";
	public static final String USER_NAME = "user_name";
	public static final String PHONE_NUMBER = "phone_number";
	
	private final static String LOCK_TABLE = "LOCK TABLE user_information IN EXCLUSIVE MODE; ";
	
	private final static String GET_HOUSE_AND_USER_INFORMATION = "SELECT * FROM house_information,user_information " +
															 "WHERE house_information.user_id=user_information.user_id ORDER BY house_id DESC";
	private final static String GET_HOUSE_INFORMATION = "SELECT * FROM house_information ORDER BY house_id DESC";
	private final static String GET_HOUSE_INFORMATION_FOR_USER = "SELECT * FROM house_information" +
								" WHERE lease='未出租' ORDER BY mark DESC,house_id DESC";
	private final static String GET_HOUSE_INFORMATION_WITH_MARK = "SELECT * FROM house_information " +
								"WHERE mark='置顶' AND allow='已付款' ORDER BY house_id DESC";
	private final static String GET_USER_HOUSE_INFORMATION_HEAD = "SELECT * FROM house_information WHERE user_id = ";
	private final static String GET_USER_HOUSE_INFORMATION_FOOT = " ORDER BY house_id DESC";
    private final static String GET_MAX_HOUSE_ID = "SELECT MAX(house_id) FROM house_information";
    private final static String INSERT_HOUSE_HEAD = "INSERT INTO house_information VALUES ( '";
    private final static String INSERT_HOUSE_FOOT = "' ) ";
    private final static String DELETE_HOUSE = "DELETE FROM house_information WHERE house_id='";
    private final static String UPDATE_HOUSE_INFORMATION = "UPDATE house_information SET ";
    private final static String SEARCH_HOUSE_FOR_USER_HEAD = "SELECT * FROM house_information WHERE lease='未出租' "; 
    private final static String SEARCH_HOUSE_FOR_USER_FOOT = " ORDER BY mark DESC,house_id DESC ";
    private final static String SEARCH_HOUSE_FOR_ADMIN_HEAD = "SELECT * FROM house_information WHERE house_id>0 "; 
    private final static String SEARCH_HOUSE_FOR_ADMIN_FOOT = " ORDER BY house_id DESC ";
    
    public static House getHouseAndUser(int house_id)
    {   
    	House house = new House();
    	try 
    	{
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(GET_HOUSE_AND_USER_INFORMATION);
            while (result.next()) 
            {
            	if(result.getInt("house_id") == house_id)
            	{          		
	                house.setUser_id(result.getInt(USER_ID));
	                house.setHouse_id(result.getInt(HOUSE_ID));
	                house.setHouse_title(result.getString(HOUSE_TITLE));
	                house.setRegion(result.getString(REGION));
	                house.setHouse_type(result.getString(HOUSE_TYPE));
	                house.setHouse_address(result.getString(HOUSE_ADDRESS));
	                house.setRent(result.getInt(RENT));
	                house.setLook_time(result.getString(LOOK_TIME));
	                house.setDescription(result.getString(DESCRIPTION));
	                house.setDirection(result.getString(DIRECTION));
	                house.setProperty_management_company(result.getString(PROPERTY_MANAGEMENT_COMPANY));
	                house.setCompleted_date(result.getString(COMPLETED_DATE));
	                house.setFloor(result.getInt(FLOOR));
	                house.setSupporting_facility(result.getString(SUPPORTING_FACILITY));
	                house.setMark(result.getString(MARK));
	                house.setWatch(result.getString(WATCH));
	                house.setAllow(result.getString(ALLOW));
	                house.setLease(result.getString(LEASE));
	                house.setUser_name(result.getString(USER_NAME));
	                house.setPhone_number(result.getString(PHONE_NUMBER));
	                break;
            	}
            }
            dbManager.close();
        } 
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return house;
    }
    
    public static ArrayList searchHouseForAdmin(String mark, String watch, String allow)
    {
    	ArrayList list = new ArrayList();
    	String searchword="";
    	if(!mark.equals("请选择"))
    	{
    		searchword+=" AND mark='" + mark + "'";
    	}
    	if(!watch.equals("请选择"))
    	{
    		searchword +=" AND watch='" +watch + "'";
    	}
    	if(!allow.equals("请选择"))
    	{
    		searchword +=" AND allow='" +allow + "'";
    	}
    	try 
    	{
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(SEARCH_HOUSE_FOR_ADMIN_HEAD + searchword + SEARCH_HOUSE_FOR_ADMIN_FOOT);
            while (result.next()) 
            {
                House house = new House();
                house.setUser_id(result.getInt(USER_ID));
                house.setHouse_id(result.getInt(HOUSE_ID));
                house.setHouse_title(result.getString(HOUSE_TITLE));
                house.setRegion(result.getString(REGION));
                house.setHouse_type(result.getString(HOUSE_TYPE));
                house.setHouse_address(result.getString(HOUSE_ADDRESS));
                house.setRent(result.getInt(RENT));
                house.setLook_time(result.getString(LOOK_TIME));
                house.setDescription(result.getString(DESCRIPTION));
                house.setDirection(result.getString(DIRECTION));
                house.setProperty_management_company(result.getString(PROPERTY_MANAGEMENT_COMPANY));
                house.setCompleted_date(result.getString(COMPLETED_DATE));
                house.setFloor(result.getInt(FLOOR));
                house.setSupporting_facility(result.getString(SUPPORTING_FACILITY));
                house.setMark(result.getString(MARK));
                house.setWatch(result.getString(WATCH));
                house.setAllow(result.getString(ALLOW));
                house.setLease(result.getString(LEASE));
                list.add(house);
            }
            dbManager.close();
        } 
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }
    
    public static ArrayList searchHouseForUser(String house_title, String region, int rent_begin, int rent_end, String house_type, String watch, int user_id)
    {
    	ArrayList list = new ArrayList();
    	String searchword="";
    	if(!house_title.equals(""))
    	{
    		searchword+=" AND house_title LIKE '%" + house_title + "%'";
    	}
    	if(!region.equals("请选择"))
    	{
    		searchword+=" AND region='" + region + "'";
    	}
    	if(rent_begin != 0 && rent_end != 0)
    	{
    		searchword +=" AND rent>=" +rent_begin + " AND rent<=" + rent_end;
    	}
    	if(!house_type.equals("请选择"))
    	{
    		searchword +=" AND house_type='" +house_type + "'";
    	}
    	if(!watch.equals("请选择"))
    	{
    		searchword +=" AND watch='" +watch + "'";
    	}
    	try 
    	{
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(SEARCH_HOUSE_FOR_USER_HEAD + searchword + SEARCH_HOUSE_FOR_USER_FOOT);
            while (result.next()) 
            {
            	if(result.getInt(USER_ID) != user_id)
            	{
	                House house = new House();
	                house.setUser_id(result.getInt(USER_ID));
	                house.setHouse_id(result.getInt(HOUSE_ID));
	                house.setHouse_title(result.getString(HOUSE_TITLE));
	                house.setRegion(result.getString(REGION));
	                house.setHouse_type(result.getString(HOUSE_TYPE));
	                house.setHouse_address(result.getString(HOUSE_ADDRESS));
	                house.setRent(result.getInt(RENT));
	                house.setLook_time(result.getString(LOOK_TIME));
	                house.setDescription(result.getString(DESCRIPTION));
	                house.setDirection(result.getString(DIRECTION));
	                house.setProperty_management_company(result.getString(PROPERTY_MANAGEMENT_COMPANY));
	                house.setCompleted_date(result.getString(COMPLETED_DATE));
	                house.setFloor(result.getInt(FLOOR));
	                house.setSupporting_facility(result.getString(SUPPORTING_FACILITY));
	                house.setMark(result.getString(MARK));
	                house.setWatch(result.getString(WATCH));
	                house.setAllow(result.getString(ALLOW));
	                house.setLease(result.getString(LEASE));
	                list.add(house);
            	}
            }
            dbManager.close();
        } 
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }
    
    public static ArrayList getUserHouse(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try 
    	{
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(GET_USER_HOUSE_INFORMATION_HEAD + user_id + GET_USER_HOUSE_INFORMATION_FOOT);
            while (result.next()) 
            {
                House house = new House();
                house.setUser_id(result.getInt(USER_ID));
                house.setHouse_id(result.getInt(HOUSE_ID));
                house.setHouse_title(result.getString(HOUSE_TITLE));
                house.setRegion(result.getString(REGION));
                house.setHouse_type(result.getString(HOUSE_TYPE));
                house.setHouse_address(result.getString(HOUSE_ADDRESS));
                house.setRent(result.getInt(RENT));
                house.setLook_time(result.getString(LOOK_TIME));
                house.setDescription(result.getString(DESCRIPTION));
                house.setDirection(result.getString(DIRECTION));
                house.setProperty_management_company(result.getString(PROPERTY_MANAGEMENT_COMPANY));
                house.setCompleted_date(result.getString(COMPLETED_DATE));
                house.setFloor(result.getInt(FLOOR));
                house.setSupporting_facility(result.getString(SUPPORTING_FACILITY));
                house.setMark(result.getString(MARK));
                house.setWatch(result.getString(WATCH));
                house.setAllow(result.getString(ALLOW));
                house.setLease(result.getString(LEASE));
                list.add(house);
            }
            dbManager.close();
        } 
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        return list;
    }
    
    public static ArrayList getAllHouse()
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_HOUSE_INFORMATION);
    		while(result.next())
    		{
    			House house = new House();
    			house.setUser_id(result.getInt(USER_ID));
                house.setHouse_id(result.getInt(HOUSE_ID));
                house.setHouse_title(result.getString(HOUSE_TITLE));
                house.setRegion(result.getString(REGION));
                house.setHouse_type(result.getString(HOUSE_TYPE));
                house.setHouse_address(result.getString(HOUSE_ADDRESS));
                house.setRent(result.getInt(RENT));
                house.setLook_time(result.getString(LOOK_TIME));
                house.setDescription(result.getString(DESCRIPTION));
                house.setDirection(result.getString(DIRECTION));
                house.setProperty_management_company(result.getString(PROPERTY_MANAGEMENT_COMPANY));
                house.setCompleted_date(result.getString(COMPLETED_DATE));
                house.setFloor(result.getInt(FLOOR));
                house.setSupporting_facility(result.getString(SUPPORTING_FACILITY));
                house.setMark(result.getString(MARK));
                house.setWatch(result.getString(WATCH));
                house.setAllow(result.getString(ALLOW));
                house.setLease(result.getString(LEASE));
                list.add(house);
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static ArrayList getAllHouseForUser(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_HOUSE_INFORMATION_FOR_USER);
    		while(result.next())
    		{
    			if(result.getInt(USER_ID) != user_id)
    			{
	    			House house = new House();
	    			house.setUser_id(result.getInt(USER_ID));
	                house.setHouse_id(result.getInt(HOUSE_ID));
	                house.setHouse_title(result.getString(HOUSE_TITLE));
	                house.setRegion(result.getString(REGION));
	                house.setHouse_type(result.getString(HOUSE_TYPE));
	                house.setHouse_address(result.getString(HOUSE_ADDRESS));
	                house.setRent(result.getInt(RENT));
	                house.setLook_time(result.getString(LOOK_TIME));
	                house.setDescription(result.getString(DESCRIPTION));
	                house.setDirection(result.getString(DIRECTION));
	                house.setProperty_management_company(result.getString(PROPERTY_MANAGEMENT_COMPANY));
	                house.setCompleted_date(result.getString(COMPLETED_DATE));
	                house.setFloor(result.getInt(FLOOR));
	                house.setSupporting_facility(result.getString(SUPPORTING_FACILITY));
	                house.setMark(result.getString(MARK));
	                house.setWatch(result.getString(WATCH));
	                house.setAllow(result.getString(ALLOW));
	                house.setLease(result.getString(LEASE));
	                list.add(house);
    			}
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static ArrayList get_house_with_mark(int user_id)
    {
    	ArrayList list = new ArrayList();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		ResultSet result = dbManager.executeQuery(GET_HOUSE_INFORMATION_WITH_MARK);
    		while(result.next())
    		{
    			if(result.getInt(USER_ID) != user_id)
    			{
	    			House house = new House();
	    			house.setUser_id(result.getInt(USER_ID));
	                house.setHouse_id(result.getInt(HOUSE_ID));
	                house.setHouse_title(result.getString(HOUSE_TITLE));
	                house.setRegion(result.getString(REGION));
	                house.setHouse_type(result.getString(HOUSE_TYPE));
	                house.setHouse_address(result.getString(HOUSE_ADDRESS));
	                house.setRent(result.getInt(RENT));
	                house.setLook_time(result.getString(LOOK_TIME));
	                house.setDescription(result.getString(DESCRIPTION));
	                house.setDirection(result.getString(DIRECTION));
	                house.setProperty_management_company(result.getString(PROPERTY_MANAGEMENT_COMPANY));
	                house.setCompleted_date(result.getString(COMPLETED_DATE));
	                house.setFloor(result.getInt(FLOOR));
	                house.setSupporting_facility(result.getString(SUPPORTING_FACILITY));
	                house.setMark(result.getString(MARK));
	                house.setWatch(result.getString(WATCH));
	                house.setAllow(result.getString(ALLOW));
	                house.setLease(result.getString(LEASE));
	                list.add(house);
    			}
    		}
    		dbManager.close();
    	}
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return list;
    }
    
    public static int getHouse_idForNew()
    {
        int maxUid = 0;
        try
        {
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(LOCK_TABLE+GET_MAX_HOUSE_ID);
            result.next();
            maxUid = result.getInt(1);
            dbManager.close();
        }
        catch (SQLException ex)
        {
            Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return maxUid+1;
    }
    
    public static void insertNewHouse(int user_id, String house_title, String region, String house_type,
    								String house_address, int rent, String look_time, String description,
    								String direction, String property_management_company, 
    								String completed_date, int floor, String supporting_facility)
    {
    	int house_id = getHouse_idForNew();
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(INSERT_HOUSE_HEAD +
    				user_id + "','" + house_id + "','" + house_title + "','" + region + "','" +
    				house_type + "','" + house_address + "','" + rent + "','" + look_time + "','" +
    				description + "','" + direction + "','" + property_management_company
    				+ "','" + completed_date + "','" + floor + "','" + supporting_facility + 
    				"','不置顶','未验证','未付款','未出租" + INSERT_HOUSE_FOOT);
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
    
    public static House getHouse(int house_id)
    {
    	House house = new House();
    	try 
    	{
            DBManager dbManager = new DBManager();
            ResultSet result = dbManager.executeQuery(GET_HOUSE_INFORMATION);
            while (result.next()) 
            {
            	if(result.getInt("house_id") == house_id)
            	{
	                house.setUser_id(result.getInt(USER_ID));
	                house.setHouse_id(result.getInt(HOUSE_ID));
	                house.setHouse_title(result.getString(HOUSE_TITLE));
	                house.setRegion(result.getString(REGION));
	                house.setHouse_type(result.getString(HOUSE_TYPE));
	                house.setHouse_address(result.getString(HOUSE_ADDRESS));
	                house.setRent(result.getInt(RENT));
	                house.setLook_time(result.getString(LOOK_TIME));
	                house.setDescription(result.getString(DESCRIPTION));
	                house.setDirection(result.getString(DIRECTION));
	                house.setProperty_management_company(result.getString(PROPERTY_MANAGEMENT_COMPANY));
	                house.setCompleted_date(result.getString(COMPLETED_DATE));
	                house.setFloor(result.getInt(FLOOR));
	                house.setSupporting_facility(result.getString(SUPPORTING_FACILITY));
	                house.setMark(result.getString(MARK));
	                house.setWatch(result.getString(WATCH));
	                house.setAllow(result.getString(ALLOW));
	                house.setLease(result.getString(LEASE));
	                break;
            	}
            }
            dbManager.close();
        } 
    	catch (SQLException ex) 
    	{
            Logger.getLogger(HouseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    	return house;
    }
    
    public static void houseInformationUpdate(int house_id, String house_title, String region, 
    										  String house_type, String house_address, int rent, 
    										  String look_time, String description, 
    										  String direction, String property_management_company, 
											  String completed_date, int floor, String supporting_facility,
											  String mark, String watch, String allow, String lease)
    {
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(UPDATE_HOUSE_INFORMATION + "house_title='" + house_title 
    				+ "', region='" + region + "', house_type='" + house_type 
    				+ "', house_address='" + house_address + "', rent='" + rent 
    				+ "', look_time='" + look_time + "', description='" + description
    				+ "', direction='" + direction
    				+ "', property_management_company='" + property_management_company 
    				+ "', completed_date='" + completed_date + "', floor='" + floor 
    				+ "', supporting_facility='" + supporting_facility + "', mark='" + mark 
    				+ "', watch='" + watch+ "', allow='" + allow  + "', lease='" + lease 
    				+ "' WHERE house_id='" + house_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
    
    public static void askForMark(int house_id)
    {
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(UPDATE_HOUSE_INFORMATION + "mark='申请置顶' WHERE house_id='" 
    								+ house_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
    
    public static void askForWatch(int house_id)
    {
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(UPDATE_HOUSE_INFORMATION + "watch='申请验证' WHERE house_id='" 
    								+ house_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }
    
    public static void deleteHouse(int house_id)
    {
    	try
    	{
    		DBManager dbManager = new DBManager();
    		dbManager.executeUpdate(DELETE_HOUSE + house_id + "'");
    		dbManager.close();
    	}
    	catch (SQLException ex)
    	{
    		Logger.getLogger(UserManager.class.getName()).log(Level.SEVERE, null, ex);
    	}
    }

}
